/**
 * 
 */
package com.mnurre.shopping.assistant;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author michael
 * Manages all connections to the underlying SQLite database.
 */
public class DataManager extends SQLiteOpenHelper {
	private static final String TAG = "DataManager";
	private static final String DATABASE = "shop_assist.db";
	private static final int VERSION = 1;
	
	// TODO Move the static table definition fields into their respective data classes
	/*
	 * Contains the table definitions for the database.
	 */
	public static final class Tables {
		
		/*
		 * The TaxRate table
		 */
		public static final class TaxRate {
			public static final String TABLE_NAME = "taxRate";
			public static final String COLUMN_ID = "_id";
			public static final String COLUMN_RATE = "rate";
			public static final String CREATE_SCRIPT =
					"create table " + TABLE_NAME + " ( "
					+ COLUMN_ID + " integer primary key autoincrement, "
					+ COLUMN_RATE + " text not null);";
		}
		
		/*
		 * The Categories table.
		 */
		public static final class Categories {
			public static final String TABLE_NAME = "categories";
			public static final String COLUMN_ID = "_id";
			public static final String COLUMN_NAME = "name";
			public static final String COLUMN_IS_TAXABLE = "isTaxable";
			public static final String CREATE_SCRIPT =
					"create table " + TABLE_NAME + " ( "
					+ COLUMN_ID + " integer primary key autoincrement, "
					+ COLUMN_NAME + " text not null, "
					+ COLUMN_IS_TAXABLE + " integer not null);";
		}
		
		/*
		 * The Products table.
		 */
		public static final class Products {
			public static final String TABLE_NAME = "products";
			public static final String COLUMN_ID = "_id";
			public static final String COLUMN_CATEGORY_ID = "categoryId";
			public static final String COLUMN_CATEGORY = "category";
			public static final String COLUMN_NAME = "name";
			public static final String COLUMN_DESCRIPTION = "description";
			public static final String COLUMN_BARCODE = "barcode";
			public static final String COLUMN_PRICE = "price";
			public static final String CREATE_SCRIPT =
					"create table " + TABLE_NAME + " ( "
					+ COLUMN_ID + " integer primary key autoincrement, "
					+ COLUMN_CATEGORY_ID + " integer not null, "
					+ COLUMN_NAME + " text not null, "
					+ COLUMN_DESCRIPTION + " text not null, "
					+ COLUMN_BARCODE + " text, "
					+ COLUMN_PRICE + " real);";
		}
		
		/*
		 * The Lists table
		 */
		public static final class Lists {
			public static final String TABLE_NAME = "lists";
			public static final String COLUMN_ID = "_id";
			public static final String COLUMN_NAME = "name";
			public static final String CREATE_SCRIPT =
					"create table " + TABLE_NAME + " ( "
					+ COLUMN_ID + " integer primary key autoincrement, "
					+ COLUMN_NAME + " text not null);";
		}
		
		/*
		 * The ProductsByList table
		 */
		public static final class ProductsByList {
			public static final String TABLE_NAME = "productsByList";
			public static final String COLUMN_ID = "_id";
			public static final String COLUMN_LIST_ID = "listId";
			public static final String COLUMN_PRODUCT_ID = "productId";
			public static final String CREATE_SCRIPT =
					"create table " + TABLE_NAME + " ( "
					+ COLUMN_ID + " integer primary key autoincrement, "
					+ COLUMN_LIST_ID + " integer not null, "
					+ COLUMN_PRODUCT_ID + " integer not null);";
		}
		
	}
	
	/*
	 * Creates a new instance of the DataManager.
	 */
	public DataManager(Context context){
		super(context, DATABASE, null, VERSION);
	}

	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		Log.d(TAG, "onCreate called. Creating the initial database.");
		db.execSQL(Tables.TaxRate.CREATE_SCRIPT);
		db.execSQL(Tables.Categories.CREATE_SCRIPT);
		db.execSQL(Tables.Products.CREATE_SCRIPT);
		db.execSQL(Tables.Lists.CREATE_SCRIPT);
		db.execSQL(Tables.ProductsByList.CREATE_SCRIPT);
		Log.d(TAG, "Done creating database.");
	}

	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.d(TAG, "onUpgrade called. Currently nothing to do.");
	}

}
